import sqlite3, json
from typing import List

DATABASE = "data.db"


# 数据库初始化
def init_db():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()

    # 创建分类表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS categories (
            id TEXT PRIMARY KEY,
            name TEXT,
            picture TEXT,
            parent_id TEXT
        )
    """)

    # 创建商品表
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS goods (
            id TEXT PRIMARY KEY,
            name TEXT,
            description TEXT,
            price TEXT,
            picture TEXT,
            discount TEXT,
            order_num TEXT,
            category_id TEXT,
            FOREIGN KEY (category_id) REFERENCES categories (id)
        )
    """)

    conn.commit()
    conn.close()


# 插入数据
def insert_data(data: List[dict]):
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()
    # print(data)
    for category in data.get("result"):
        print(category)
        print(type(category))

        cursor.execute(
            """
            INSERT OR IGNORE INTO categories (id, name, picture, parent_id)
            VALUES (?, ?, ?, ?)
        """,
            (category["id"], category["name"], category["picture"], None),
        )

        # 插入子分类
        if category.get("children"):
            for child in category["children"]:
                cursor.execute(
                    """
                    INSERT OR IGNORE INTO categories (id, name, picture, parent_id)
                    VALUES (?, ?, ?, ?)
                """,
                    (child["id"], child["name"], child["picture"], category["id"]),
                )

        # 插入商品
        if category.get("goods"):
            for good in category["goods"]:
                cursor.execute(
                    """
                    INSERT OR IGNORE INTO goods (id, name, description, price, picture, discount, order_num, category_id)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                """,
                    (
                        good["id"],
                        good["name"],
                        good["desc"],
                        good["price"],
                        good["picture"],
                        good.get("discount"),
                        good.get("orderNum"),
                        category["id"],
                    ),
                )

    conn.commit()
    conn.close()


# 从数据库中获取数据
def get_category():
    conn = sqlite3.connect(DATABASE)
    cursor = conn.cursor()

    # 获取所有分类
    cursor.execute("SELECT id, name, picture, parent_id FROM categories")
    categories = cursor.fetchall()

    # 获取所有商品
    cursor.execute(
        "SELECT id, name, description, price, picture, discount, order_num, category_id FROM goods"
    )
    goods = cursor.fetchall()
    conn.close()

    # 构建分类树
    category_map = {
        cat[0]: {
            "id": cat[0],
            "name": cat[1],
            "picture": cat[2],
            "children": [],
            "goods": [],
        }
        for cat in categories
    }

    for cat in categories:
        if cat[3]:  # 如果有父分类
            category_map[cat[3]]["children"].append(category_map[cat[0]])

    for good in goods:
        if good[7]:  # 商品关联分类
            category_map[good[7]]["goods"].append(
                {
                    "id": good[0],
                    "name": good[1],
                    "desc": good[2],
                    "price": good[3],
                    "picture": good[4],
                    "discount": good[5],
                    "orderNum": good[6],
                }
            )

    # 返回顶级分类
    return [
        cat
        for cat in category_map.values()
        if cat["id"] not in [c[3] for c in categories if c[3]]
    ]


def startup_event():
    init_db()
    with open("temp.json", "r", encoding="utf-8") as f:
        data = json.load(f)
        insert_data(data)


if __name__ == "__main__":
    startup_event()
